/*

Input: corplat_kreise_yrl.dta [county-level stats from Corona-Datenplattform]
		> Raw data avlb at: https://www.healthcare-datenplattform.de/ [accessed: June 23]

Output: Table E.1
		
Tasks: Compare Munich to top 20 counties across sociodem characteristics

*/

*--- PULL Corona datenplattform, county-level data, 2019
use "$rawdata/CoronaDatenplattform/corplat_kreise_yrl.dta", clear

// id for 19 biggest cities in GER + Munich
gen id = inlist(ags5,9162, 11000, 2000, 5315, 6412, 8111, 5111, 14713, 5913, 5113, 4011, 14612, 3241, 9564, 5112,5911,5124,5711,5314,5515)
replace id=. if id==0


** Genvars
gen 	shr_65plus = (kr_ew_65u75+ kr_ew_75)/kr_ew_19

gen 	shr_wa_pop =(kr_ew_15u18+ kr_ew_18u20 +kr_ew_20u25 +kr_ew_25u30 +kr_ew_30u35 ///
					+kr_ew_35u40 +kr_ew_40u45 +kr_ew_45u50 +kr_ew_50u55 +kr_ew_55u60 +kr_ew_60u65) / kr_ew_19 

gen 	shr_abi = kr_schul_hoch/kr_ew_19

replace kr_selbst =. if kr_selbst<0
gen 	shr_selbst = kr_selbst/kr_erwt_ao

gen 	shr_uni = (kr_schul_hoch +kr_beruf_hochschul)/kr_ew_19

gen 	shr_foreign = kr_ausl_anz/kr_ew_19

gen 	pop1000 = kr_ew_19/1000

lab var pop1000 	"Population (in thsd)"
lab var shr_65plus  "\% Population Aged 65+"
lab var shr_wa_pop  "\% Working Age Population (15-65)"
lab var kr_ein_md	"Median Income (in euros)"
lab var kr_btw_ant	"Turnout, Federal Election 2017 (in \%)"
lab var shr_selbst  "\% Self Employed"
lab var shr_uni		"\% College Educated"
lab var shr_foreign "\% Foreigners"
lab var kr_bsdl_dichte "Population density (in inhab. per sqkm)"

* Create Table

// variables
global vars pop1000 kr_bsdl_dichte shr_65plus shr_wa_pop shr_uni shr_foreign shr_selbst kr_ein_md kr_btw_ant 

foreach v in $vars {
	gsort id -`v'
	gen rk_`v' = _n if id!=.
}


tabstat $vars  if id!=., stat(min mean max) format(%12.2f)
tabstat $vars if ags5==9162,  format(%12.2f)

  
	* TABLE E1. Representativeness of Munich
	eststo clear
	eststo m_ger: estpost tabstat $vars, statistics(min max mean) col(statistics)
	eststo m_top: estpost tabstat $vars if id!=., statistics(min max mean) col(statistics)
	eststo m_muc: estpost tabstat $vars if ags5==9162, statistics(mean) col(statistics)
	// replace variable with MUC rank value
	foreach v in $vars {
		replace `v'=rk_`v'
	}	
	eststo m_mucrk: estpost tabstat $vars if ags5==9162, statistics(mean) col(statistics)
	esttab m_ger m_top m_muc m_mucrk using "$tables/Table_E1_muc_represent.tex", replace label substitute(# \#) ///
		fragment nonumber  noobs type ///
		mtitle("Germany" "Top 20" "Munich" "") cells(" min(label(Min) fmt(2)) mean(label(Mean) fmt(2)) max(label(Max) fmt(2))")
